<?php

class ModelSegurosSeguros extends Model {
	
	
	public function editarCotizacion($cotizacion_id, $data) {
		
				$this->db->query("UPDATE  cotizacion SET  contratante_persona = '" . $this->db->escape($data['cotizacion_persona']) . "', armadora = '" . $this->db->escape($data['cotizacion-armadora']) . "', marca = '" . $this->db->escape($data['cotizacion-marca']) . "', version = '" . $this->db->escape($data['cotizacion_version']) . "', estado = '" . $this->db->escape($data['cotizacion_estado']) . "', codigo_postal = '" . $this->db->escape($data['cotizacion_cp']) . "', cobertura = '" . $this->db->escape($data['cotizacion_cobertura']) . "', forma_de_pago = '" . $this->db->escape($data['cotizacion_formapago']) . "', estatus = '" . $this->db->escape($data['cotizacion_estatus']) . "' ,fecha_modificacion= NOW()  WHERE cotizacion_id=".(int)$cotizacion_id);							
	}
				
		
				
				
				
				
				
				

	 
	
	
	
	public function copyProduct($product_id) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'");
		
		if ($query->num_rows) {
			$data = array();
			
			$data = $query->row;
			
			$data['sku'] = '';
			$data['upc'] = '';
			$data['viewed'] = '0';
			$data['keyword'] = '';
			$data['status'] = '0';
						
			$data = array_merge($data, array('product_attribute' => $this->getProductAttributes($product_id)));
			$data = array_merge($data, array('product_description' => $this->getProductDescriptions($product_id)));			
			$data = array_merge($data, array('product_discount' => $this->getProductDiscounts($product_id)));
			$data = array_merge($data, array('product_filter' => $this->getProductFilters($product_id)));
			$data = array_merge($data, array('product_image' => $this->getProductImages($product_id)));		
			$data = array_merge($data, array('product_option' => $this->getProductOptions($product_id)));
			$data = array_merge($data, array('product_related' => $this->getProductRelated($product_id)));
			$data = array_merge($data, array('product_reward' => $this->getProductRewards($product_id)));
			$data = array_merge($data, array('product_special' => $this->getProductSpecials($product_id)));
			$data = array_merge($data, array('product_category' => $this->getProductCategories($product_id)));
			$data = array_merge($data, array('product_download' => $this->getProductDownloads($product_id)));
			$data = array_merge($data, array('product_layout' => $this->getProductLayouts($product_id)));
			$data = array_merge($data, array('product_store' => $this->getProductStores($product_id)));
			
			$this->addProduct($data);
		}
	}
	
	public function deleteProduct($product_id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_filter WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_option WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_option_value WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE related_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_reward WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_download WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_store WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "review WHERE product_id = '" . (int)$product_id . "'");
		
		$this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id. "'");
		
		$this->cache->delete('product');
	}
	
	public function insertarCotizacionOpciones($cotizacion_id,$data) {
	
		$this->db->query("INSERT INTO cotizaciones_opciones SET cotizacion_id = " . (int)$cotizacion_id . ", aseguradora_id= '" . (int)$data['aseguradora_id'] . "', costo = " . (double)$data['costo'] . ", dan_mat_parc = '" . $this->db->escape($data['dan_mat_parc']) . "', robo_total = '" . $this->db->escape($data['robo_total']) . "', cristales = '" . $this->db->escape($data['cristales']) . "', responsabilidad_civil = '" . (double)$data['responsabilidad_civil'] . "', proteccion_legal = '" . $this->db->escape($data['proteccion_legal']) . "', gas_med_ocu = '" . $this->db->escape($data['gas_med_ocu']) . "'");								
                
           
		
		
    }
    
    public function obtenerCotizacionOpciones($cotizacion_id) {
    
    	$cotizacion_opciones_datos = array();
    	
    	$query = $this->db->query("SELECT * FROM cotizaciones_opciones WHERE  cotizacion_id = " . (int)$cotizacion_id );
    	
    	
    	
    	
    	
    	
    	foreach ($query->rows as $result) {
    		$cotizacion_opciones_datos[$result['aseguradora_id']] = array(
    				'costo'            		 => $result['costo'],
    				'dan_mat_parc'    		 => $result['dan_mat_parc'],
    				'robo_total'     		 => $result['robo_total'],
    				'cristales' 			 => $result['cristales'],
    				'responsabilidad_civil'  => $result['responsabilidad_civil'],
    				'proteccion_legal' 		 => $result['proteccion_legal'],
    				'gas_med_ocu' 			 => $result['gas_med_ocu'],
    				
    		);
    	}
    	
    	return $cotizacion_opciones_datos;
    	
    	
    	
  }
    
	
	public function getCotizacion($cotizacion_id) {
		$query = $this->db->query("SELECT * FROM cotizacion  c INNER JOIN usuario_cotizacion uc ON (uc.usuario_cotizacion_id = c.usuario_cotizacion_id) INNER JOIN cotizacion_estatus ce ON ( ce.cotizacion_estatus_id = c.estatus ) WHERE cotizacion_id=" . (int)$cotizacion_id );
				
		return $query->row;
	}
	
	public function getCotizaciones($data = array()) {
		
		
		
		$sql = "SELECT * FROM cotizacion  c INNER JOIN usuario_cotizacion uc ON (uc.usuario_cotizacion_id = c.usuario_cotizacion_id) ";
		
// 		if (!empty($data['filter_category_id'])) {
// 			$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";			
// 		}
				
		$sql .= " WHERE 1 "; 
		
		if (!empty($data['filter_num_cotizacion'])) {
			$sql .= " AND c.cotizacion_id =" . $this->db->escape($data['filter_num_cotizacion']);
		}
		
		if (!empty($data['filter_nombre'])) {
			$sql .= " AND uc.nombre LIKE '" . $this->db->escape($data['filter_nombre']) . "%'";
		}

		if (!empty($data['filter_cobertura'])) {
			$sql .= " AND c.cobertura LIKE '" . $this->db->escape($data['filter_cobertura']) . "%'";
		}
		
		if (!empty($data['filter_estado'])) {
			$sql .= " AND c.estado LIKE '" . $this->db->escape($data['filter_estado']) . "%'";
		}
		
		if (isset($data['filter_fecha']) && !is_null($data['filter_fecha'])) {
			$sql .= " AND DATE(c.fecha_creacion) LIKE '" . $this->db->escape($data['filter_fecha']) . "%'";
		}
		
		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND c.estatus = '" . (int)$data['filter_status'] . "'";
		}
		
		$sql .= " GROUP BY c.cotizacion_id";
					
		$sort_data = array(
			'c.cotizacion_id',
			'uc.nombre',
			'c.cobertura',
			'c.estado',
			'c.fecha_creacion',
			'c.estatus'
			
		);	
		
		//TODO:ESTA parte de aqui hasta terminar el metodo esta asi esta bien
		
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];	
		} else {
			$sql .= " ORDER BY uc.nombre";	
		}
		
		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
	
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}				

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
		
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}	
		
		//echo $sql; 
		$query = $this->db->query($sql);
	
		return $query->rows;
	}
	
	//ESTATUS DE LA COTIZACION
	public function getCotizacionEstatus($estatus_id) {
		 $query = $this->db->query("SELECT descripcion_estatus AS descripcion_estatus FROM cotizacion_estatus WHERE cotizacion_estatus_id=" .(int)$estatus_id);
	
		return $query->row['descripcion_estatus'];
	}
	
	
	public function obtenerArmadoras() {
	
		$query = $this->db->query("SELECT armadora_autos_id as armadora_autos_id, descripcion_armadora as descripcion_armadora  FROM  armadora_autos WHERE 1 order by armadora_autos_id");
		return $query->rows;
	
	}
	
	public function obtenerMarcas($armadara_autos_id) {
		$query =$this->db->query("SELECT marcas_autos_id as marcas_autos_id, armadora_autos_id as armadora_autos_id, descripcion_marcas as descripcion_marcas FROM  marcas_autos  WHERE armadora_autos_id =".(int)$armadara_autos_id);
		return $query->rows;
	}
	
	public function obtenerStatusCotizacion() {
		$query =$this->db->query("SELECT * FROM  cotizacion_estatus");
		return $query->rows;
	}
	
	public function actualizarStatusCotizacion($cotizacion_id,$cotizacion_estatus_id) {
		$query =$this->db->query("UPDATE cotizacion SET  estatus=".(int)$cotizacion_estatus_id . " WHERE cotizacion_id=".(int)$cotizacion_id);
		
	}
	
	
	public function obtenerTotalMarcas($armadara_autos_id) {
		$query =$this->db->query("SELECT count(*)  as total FROM marcas_autos WHERE armadora_autos_id =".(int)$armadara_autos_id);
		if (isset($query->row['total'])) {
			return $query->row['total'];
		} else {
			return 0;
		}
	
	}
	
	public function getTotalCotizaciones($data = array()) {
		$sql = "SELECT COUNT(*) AS total FROM cotizacion c INNER JOIN  usuario_cotizacion uc on (uc.usuario_cotizacion_id = c.usuario_cotizacion_id) ";
	
		// 		if (!empty($data['filter_category_id'])) {
		// 			$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";
		// 		}
			
		$sql .= " WHERE 1";
	
		if (!empty($data['filter_nombre'])) {
			$sql .= " AND uc.nombre LIKE '" . $this->db->escape($data['filter_nombre']) . "%'";
		}
	
		if (!empty($data['filter_cobertura'])) {
			$sql .= " AND c.cobertura LIKE '" . $this->db->escape($data['filter_cobertura']) . "%'";
		}
	
		if (!empty($data['filter_estado'])) {
			$sql .= " AND c.estado LIKE '" . $this->db->escape($data['filter_estado']) . "%'";
		}
	
		if (isset($data['filter_fecha']) ) {
			$sql .= " AND c.fecha_creacion = '" . $this->db->escape($data['filter_fecha']) . "'";
		}
	
		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND c.estatus = '" . (int)$data['filter_status'] . "'";
		}
	
		$query = $this->db->query($sql);
	
		return $query->row['total'];
	}
	
	
	
	
	
	
}
?>
